﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace SQL_Provider
{
    public class Provider
    {
        string cnnectionString = "";
        public Provider()
        {
            cnnectionString = ConfigurationManager.AppSettings["KetNoiCSDL"];
        }
        public Provider(string s)
        {
            cnnectionString = s;
        }
        public SqlConnection GetConnection()
        {
            SqlConnection cnn = new SqlConnection(cnnectionString);
            return cnn;
        }
        //goi thuc thi StoredProcedure SPName voi tham so truyen vao para,tra ve ket qua kieu int
        public int Execute_Modify(string SPName, SqlParameter[] para, CommandType cmdType)
        {
            SqlConnection cnn = new SqlConnection(cnnectionString);
            try
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(SPName, cnn);
                cmd.CommandType = cmdType;
                if (para != null)
                    cmd.Parameters.AddRange(para);
                return cmd.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            finally
            {
                cnn.Close();
            }
        }

        public int Execute_Modify(string cmdText)
        {
            SqlConnection cnn = new SqlConnection(cnnectionString);
            try
            {
                cnn.Open();
                SqlCommand cmd = cnn.CreateCommand();
                cmd.CommandText = cmdText;
                return cmd.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            finally
            {
                cnn.Close();
            }
        }
        //goi thuc thi StoredProcedure SPName voi tham so truyen vao para,tra ve 1 doi tuong kieu DataTable
        public DataTable Execute_Table(string SPName, SqlParameter[] para, CommandType cmdType)
        {
            SqlConnection cnn = new SqlConnection(cnnectionString);
            try
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(SPName, cnn);
                cmd.CommandType = cmdType;
                if (para != null)
                    cmd.Parameters.AddRange(para);
                DataTable table = new DataTable();
                table.Load(cmd.ExecuteReader());
                return table;
            }
            catch
            {
                return null;
            }
            finally
            {
                cnn.Close();
            }
        }
        //goi thuc thi StoredProcedure SPName voi tham so truyen vao para,tra ve 1 doi tuong kieu object
        public object Execute_Scalar(string SPName, SqlParameter[] para, CommandType cmdType)
        {
            SqlConnection cnn = new SqlConnection(cnnectionString);
            try
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(SPName, cnn);
                cmd.CommandType = cmdType;
                if (para != null)
                    cmd.Parameters.AddRange(para);
                return cmd.ExecuteScalar();
            }
            catch
            {
                return null;
            }
            finally
            {
                cnn.Close();
            }
        }
        //--------
        public DataTable Execute_Table(string cmdText)
        {
            SqlConnection cnn = new SqlConnection(cnnectionString);
            try
            {
                cnn.Open();
                SqlCommand cmd = cnn.CreateCommand();
                cmd.CommandText = cmdText;
                //tao doi tuong DataAdapter
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                // tao doi tuong DataTable de chua du lieu
                DataTable dt = new DataTable();

                //truyen du lieu vao DataTable
                da.Fill(dt);
                cnn.Close();
                cnn.Dispose();
                da.Dispose();

                return dt;

            }
            catch
            { return null; }
        }

        public SqlDataReader Execute_Reader(string SPName, SqlParameter[] para, CommandType cmdType)
        {
            SqlConnection cnn = new SqlConnection(cnnectionString);
            try
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand(SPName, cnn);
                cmd.CommandType = cmdType;
                if (para != null)
                    cmd.Parameters.AddRange(para);
                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
            catch
            { return null; }
        }

        public SqlDataReader Execute_Reader(string cmdText)
        {
            SqlConnection cnn = new SqlConnection(cnnectionString);
            try
            {
                cnn.Open();
                SqlCommand cmd = cnn.CreateCommand();
                cmd.CommandText = cmdText;
                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
            catch
            { return null; }
        }
    }
}
